﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web;

/// <summary>
/// Summary description for ContractInfoGetFromOtherDB
/// </summary>
public class ContractInfoGetFromOtherDB
{
    SqlHelp help = new SqlHelp();
    private int m = 0;

	public ContractInfoGetFromOtherDB()
	{	}
    
    #region Plant
    public int PlantUpdate(ContractModel model, int n)
    {
        Log.WritLog("Readied for Plant update", System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        m = 0;
        DataTable dt = help.GetDataTable("select * from B_DBLoginSet where DBForFlag='2'");
        if (dt != null && dt.Rows.Count > 0)
        {
            string coCode = model.CoCode;
            string userCode = model.UserCode;
            string strWhere = string.Empty;
            if (model.contractNos.Length > 3)
            { strWhere = " and " + CommonModel.ContractNoColumnTrading + " in (" + model.contractNos + "'#@')"; }

            foreach (DataRow rowDB in dt.Rows)
            {
                DataTable ddtt = new DataTable();
                if (rowDB["DBType"].ToString().Equals("0"))
                {
                    SqlHelp helpSql = new SqlHelp(string.Format(CommonModel.sqlConnectString,
                        rowDB["DBServerIP"], rowDB["DBName"], rowDB["DBLoginName"], rowDB["DBLoginPwd"]));
                    SqlParameter condictionPram = new SqlParameter("@contractNos", model.contractNos + "'#@'");
                    SqlParameter[] prams = new SqlParameter[] { condictionPram };
                    ddtt = helpSql.RunDataTableProc("GetInfoByContractNosForPlantgUpdate", prams);
                    Log.WritLog(string.Format("Have got {0} rows.", (ddtt != null) ? ddtt.Rows.Count.ToString() : "0", m), " SQL ");
                }
                else 
                {
                    SqlHelp helpOle = new SqlHelp(string.Format(CommonModel.oleConnectString,
                        rowDB["DBServerIP"], rowDB["DBLoginName"], rowDB["DBLoginPwd"]));

                    //查询NJ数据
                    string strSql = string.Format(@"SELECT distinct HCPO PlantCustomerPONo,HORD PlantSoNo,HCUST PlantCustomerNo,CNME PlantCustomerName,
                                    LLINE PlantLineNo,LPROD PlantItemNo,SORD PlantWONo,LDESC PlantDescription1,LDESC PlantDescription2,
                                    LQORD PlantOrderQty,LQSHP PlantShippedQTY,LNET PlantValue,HCURR PlantCurrency,
                                    LODTE PlantOrderEnterDate,LRDTE PlantOADate,LSDTE PlantRescheduleDate,SRDTE PlantWOReleaseDate,
                                    LPLIN,LUDTE1,LUDTE2,SCORD,SOLOT,SORD MP64001,CLCPOL PlantLineNoForTranding,
                                    SDDTE PlantGoodReadyDate,SQREQ PlantWOQty,'' PlantWOEntryDate,SQFIN PlantWOFinishedQTY                                    
                                    FROM ERPLXP61F.ECH ch join ERPLXP61F.ECL cl on ch.HORD=cl.LORD 
                                    left join ERPLXP61F.FSO fs on ch.HORD=fs.SCORD and CHAR(LLINE)=CHAR(SOLOT) 
                                    left join ERPLXP61F.RCM rc on HCUST=CCUST 
                                    where HCPO is not null and length(LTRIM(RTRIM(HCPO)))>0 and HCPO in ({0}'#@')", model.contractNos);

                    OleDbCommand sqlc = new OleDbCommand(strSql);
                    ddtt = helpOle.GetOLEDataTable(sqlc);
                    Log.WritLog(string.Format("Have got {0} rows.", (ddtt != null) ? ddtt.Rows.Count.ToString() : "0", m.ToString()), " DB2 ");        
                }

                if (ddtt != null && ddtt.Rows.Count > 0)
                {
                    string contractNo = string.Empty;
                    string contractNoCustomer = string.Empty;
                    DataTable plantDT = model.ContractNoForPlantUpdateDT;
                    if (plantDT != null && plantDT.Rows.Count > 0)
                    {
                        string date = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                        DataRow[] contractNoRow;
                        foreach (DataRow row in ddtt.Rows)
                        {
                            if (row["PlantCustomerPONo"] != null && row["PlantCustomerPONo"].ToString().Trim().Length > 0)
                            {
                                if (CommonMethod.CheckNUM(row["PlantCustomerPONo"].ToString()))
                                { contractNoRow = plantDT.Select(string.Format("TradingPO='{0}'", Convert.ToInt32(row["PlantCustomerPONo"].ToString()))); }
                                else
                                { contractNoRow = plantDT.Select(string.Format("TradingPO='{0}'", row["PlantCustomerPONo"].ToString())); }

                                if (contractNoRow != null && contractNoRow.Length == 0)
                                { contractNoRow = plantDT.Select(string.Format("TradingPO='{0}'", row["PlantCustomerPONo"].ToString())); }

                                if (contractNoRow != null && contractNoRow.Length > 0)
                                {
                                    contractNo = contractNoRow[0]["ContractNoMain"].ToString();
                                    contractNoCustomer = contractNoRow[0]["ContractNoCust"].ToString();
                                    #region
                                    m += help.RunProcInt("UpdatePlant",
                                            new SqlParameter[] 
                                            {
                                                new SqlParameter("@CoCode",model.CoCode),
                                                new SqlParameter("@ContractNoMain",contractNo),
                                                new SqlParameter("@ContractNoCust",contractNoCustomer),
                                                new SqlParameter("@PlantSoNo",row["PlantSoNo"].ToString()),
                                                new SqlParameter("@PlantCustomerPONo",row["PlantCustomerPONo"].ToString()),
                                                new SqlParameter("@PlantLineNo",row["PlantLineNo"].ToString()),
                                                new SqlParameter("@PlantLineNoForTranding",row["PlantLineNoForTranding"].ToString()),
                                                new SqlParameter("@PlantCustomerNo",row["PlantCustomerNo"].ToString()),
                                                new SqlParameter("@PlantCustomerName",row["PlantCustomerName"].ToString()),
                                                new SqlParameter("@PlantItemNo",row["PlantItemNo"].ToString()),
                                                new SqlParameter("@PlantDescription1",row["PlantDescription1"].ToString()),
                                                new SqlParameter("@PlantDescription2",row["PlantDescription2"].ToString()),
                                                new SqlParameter("@PlantOrderQty",row["PlantOrderQty"].ToString()),
                                                new SqlParameter("@PlantValue",row["PlantValue"].ToString()),
                                                new SqlParameter("@PlantCurrency",row["PlantCurrency"].ToString()),
                                                new SqlParameter("@PlantShippedQTY",row["PlantShippedQTY"].ToString()),
                                                new SqlParameter("@PlantOrderEnterDate",row["PlantOrderEnterDate"].ToString()),
                                                new SqlParameter("@PlantOADate",row["PlantOADate"].ToString()),
                                                new SqlParameter("@PlantRescheduleDate",row["PlantRescheduleDate"].ToString()),
                                                new SqlParameter("@PlantWONo",row["PlantWONo"].ToString()),
                                                new SqlParameter("@PlantWOEntryDate",row["PlantWOEntryDate"].ToString()),
                                                new SqlParameter("@PlantWOQty",row["PlantWOQty"].ToString()),
                                                new SqlParameter("@PlantWOFinishedQTY",row["PlantWOFinishedQTY"].ToString()),
                                                new SqlParameter("@PlantWOReleaseDate",row["PlantWOReleaseDate"].ToString()),
                                                new SqlParameter("@PlantGoodReadyDate",row["PlantGoodReadyDate"].ToString()),
                                                new SqlParameter("@UpdateAuthor",model.UserCode),
                                                new SqlParameter("@UpdateDate",date)
                                            });
                                    #endregion
                                }
                            }
                        }
                        Log.WritLog(string.Format("Have got {0} rows. Have Updated {1} Rows For Plant.",
                            ddtt.Rows.Count, m), "Begin Time: " + date);
                    }
                }
            }
        }
        Log.WritLog("Finished Plant update", System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        return m;
    }
    #endregion

    #region Trading
    public int OrderSalesCatalogCustomerTrading(ContractModel model)
    {
        Log.WritLog("Readied for Trading to Get data", System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        m = 0;
        string coCode = model.CoCode;
        string userCode = model.UserCode;
        string strWhere = string.Empty;
        if (model.contractNos.Length > 3)
        { strWhere = " and " + CommonModel.ContractNoColumnTrading + " in (" + model.contractNos + "'')"; }

        DataTable dt = help.GetDataTable("select * from B_DBLoginSet where DBForFlag='1'");
        if (dt != null && dt.Rows.Count > 0)
        {
            foreach (DataRow rowDB in dt.Rows)
            {
                DataTable[] ddtt = new DataTable[] { new DataTable(), new DataTable() };
                if (rowDB["DBType"].ToString().Equals("0"))
                {
                    SqlHelp sqlHelp = new SqlHelp(string.Format(CommonModel.sqlConnectString,
                        rowDB["DBServerIP"].ToString(), rowDB["DBName"].ToString(), rowDB["DBLoginName"].ToString(), rowDB["DBLoginPwd"].ToString()));              
                    SqlParameter condictionPram = new SqlParameter("@contractNos", model.contractNos + "'#@'");
                    SqlParameter[] prams = new SqlParameter[] { condictionPram };
                    ddtt = sqlHelp.RunProcGetTables("GetInfoByContractNosForTradingUpdate", prams);
                }
                if (ddtt != null && ddtt.Length > 1)
                {
                    #region
                    string date = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    if (ddtt[0] != null && ddtt[0].Rows.Count > 0)
                    {
                        foreach (DataRow row in ddtt[0].Rows)
                        {
                            #region Customer
                            m += help.RunProcInt("UpdateCustomer",
                                        new SqlParameter[] 
                                {
                                    new SqlParameter("@CoCode",coCode),
                                    new SqlParameter("@ContractNoMain",row["ContractNoMain"].ToString().Trim()),
                                    new SqlParameter("@ContractNoCust",row["ContractNoCust"].ToString().Trim()),
                                    new SqlParameter("@CustomerNo",row["CustomerNo"].ToString()),
                                    new SqlParameter("@CustomerName",row["CustomerName"].ToString()),
                                    new SqlParameter("@CustomerType",row["CustomerType"].ToString()),
                                    new SqlParameter("@CustomerEndUserName",row["CustomerEndUserName"].ToString()),
                                    new SqlParameter("@UpdateAuthor",userCode),
                                    new SqlParameter("@UpdateDate",date)
                                });
                            #endregion

                            #region SalesCataLog

                            m += help.RunProcInt("UpdateSalesCatalog",
                               new SqlParameter[] 
                                {
                                    new SqlParameter("@CoCode",coCode),
                                    new SqlParameter("@ContractNoMain",row["ContractNoMain"].ToString().Trim()),
                                    new SqlParameter("@ContractNoCust",row["ContractNoCust"].ToString().Trim()),
                                    new SqlParameter("@SalesName",row["SalesName"].ToString()),
                                    new SqlParameter("@RSMName",row["RSMName"].ToString()),
                                    new SqlParameter("@GSMName",row["GSMName"].ToString()),
                                    new SqlParameter("@UpdateAuthor",userCode),
                                    new SqlParameter("@UpdateDate",date)
                                });

                            #endregion

                            #region Order

                            m += help.RunProcInt("UpdateOrder",
                                new SqlParameter[] 
                                {
                                    new SqlParameter("@CoCode",coCode),
                                    new SqlParameter("@ContractNoMain",row["ContractNoMain"].ToString().Trim()),
                                    new SqlParameter("@ContractNoCust",row["ContractNoCust"].ToString().Trim()),
                                    new SqlParameter("@OrderNo",row["OrderNo"].ToString()),
                                    new SqlParameter("@OHName",row["OHName"].ToString()),
                                    new SqlParameter("@OrderPlacementData",row["OrderPlacementData"].ToString()),
                                    new SqlParameter("@OrderValue",row["OrderValue"].ToString()),
                                    new SqlParameter("@OrderCurrency",row["OrderCurrency"].ToString()),
                                    new SqlParameter("@OrderValueUSD",row["OrderValueUSD"].ToString()),
                                    new SqlParameter("@OrderRequestDeliveryDateCRD",row["OrderRequestDeliveryDateCRD"].ToString()),
                                    new SqlParameter("@OriginalOADate",row["OriginalOADate"].ToString()),
                                    new SqlParameter("@OAScheduledDeliveryDate",row["OAScheduledDeliveryDate"].ToString()),
                                    new SqlParameter("@UpdateAuthor",userCode),
                                    new SqlParameter("@UpdateDate",date)
                                });

                            #endregion
                        }
                        Log.WritLog(string.Format("Have got {0} rows. Have Updated {1} Rows For Head.",
                            ddtt[0].Rows.Count.ToString(), m.ToString()), "Begin Time: " + date);
                    }
                    if (ddtt[1] != null && ddtt[1].Rows.Count > 0)
                    {
                        #region
                        m = 0;
                        foreach (DataRow row in ddtt[1].Rows)
                        {
                            m += help.RunProcInt("UpdateTrading",
                                new SqlParameter[] 
                                {
                                    new SqlParameter("@CoCode",coCode),
                                    new SqlParameter("@ContractNoMain",row["ContractNoMain"].ToString().Trim()),
                                    new SqlParameter("@ContractNoCust",row["ContractNoCust"].ToString().Trim()),
                                    new SqlParameter("@TradingSoNo",row["TradingSoNo"].ToString()),
                                    new SqlParameter("@TradingSoLineNo",row["TradingSoLineNo"].ToString()),
                                    new SqlParameter("@TradingProductNo",row["TradingProductNo"].ToString()),
                                    new SqlParameter("@TradingDescription1",row["TradingDescription1"].ToString()),
                                    new SqlParameter("@TradingDescription2",row["TradingDescription2"].ToString()),
                                    new SqlParameter("@TradingTextLine",row["TradingTextLine"].ToString()),
                                    new SqlParameter("@TradingOrderQty",row["TradingOrderQty"].ToString()),
                                    new SqlParameter("@TradingCurrency",row["TradingCurrency"].ToString()),
                                    new SqlParameter("@TradingValueBreakDown",row["TradingValueBreakDown"].ToString()),
                                    new SqlParameter("@TradingUnitPrice",row["TradingUnitPrice"].ToString()),
                                    new SqlParameter("@TradingSupplierOA",row["TradingSupplierOA"].ToString()),
                                    new SqlParameter("@TradingPO",row["TradingPO"].ToString()),
                                    new SqlParameter("@TradingVender",row["TradingVender"].ToString()),
                                    new SqlParameter("@TradingPOLine",row["TradingPOLine"].ToString()),
                                    new SqlParameter("@TradingPOEntryDate",row["TradingPOEntryDate"].ToString()),
                                    new SqlParameter("@TradingPORequiredDate",row["TradingPORequiredDate"].ToString()),
                                    new SqlParameter("@TradingPOCleanOrderDate",row["TradingPOCleanOrderDate"].ToString()),
                                    new SqlParameter("@TradingShippedQty",row["TradingShippedQty"].ToString()),
                                    new SqlParameter("@UpdateAuthor",userCode),
                                    new SqlParameter("@UpdateDate",date)
                                });
                        }
                        #endregion
                        Log.WritLog(string.Format("Have got {0} rows. Have Updated {1} Rows For Tading.",
                            ddtt[1].Rows.Count.ToString(), m.ToString()), "Begin Time: " + date);
                    }
                    #endregion
                }
            }
        }
        else
        {
            Log.WritLog("Can not connect DataBase!", " Can not connect DataBase! ");
        }
        Log.WritLog("Finished Trading update", System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        return m;
    }
    #endregion

    // Update All
    public void UpdateTradingPlant()
    {
        ContractModel modelT = new ContractModel();
        modelT.UserCode = "Admin";
        modelT.CoCode = "22";
        DataTable[] dts = UpdateTradingPlantGetNos();

        #region Trading
        DataTable dt = dts[0];
        if (dt.Rows.Count <= 250)
        {
            modelT.contractNos = GetAllContractNo(dt, "ContractNoMain");
            OrderSalesCatalogCustomerTrading(modelT);
        }
        else
        {
            int nStart = 0, length = dt.Rows.Count, nEnd = 0;
            string temp = string.Empty;
            while (nStart < length)
            {
                temp = string.Empty;

                nEnd = (nStart + 300) > length ? length : (nStart + 300);
                for (int i = nStart; i < nEnd; i++)
                {
                    temp += "'" + dt.Rows[i]["ContractNoMain"].ToString() + "',";
                }
                if (temp.Length > 1)
                {
                    modelT.contractNos = temp;
                    OrderSalesCatalogCustomerTrading(modelT);
                }
                nStart = nEnd;
            }
        }
        #endregion

        #region Plant
        DataTable dtPlant = dts[1];
        modelT.ContractNoForPlantUpdateDT = dtPlant;
        if (dtPlant.Rows.Count <= 250)
        {
            modelT.contractNos = GetAllContractNo(dtPlant, "TradingPO");
            PlantUpdate(modelT, 2);
        }
        else
        {
            #region
            int nStart = 0, length = dtPlant.Rows.Count, nEnd = 0;
            string temp = string.Empty;
            while (nStart < length)
            {
                temp = string.Empty;

                nEnd = (nStart + 250) > length ? length : (nStart + 250);
                for (int i = nStart; i < nEnd; i++)
                {
                    if (CommonMethod.CheckNUM(dtPlant.Rows[i]["TradingPO"].ToString()))
                    { temp += string.Format("'{0}',", Convert.ToInt64(dtPlant.Rows[i]["TradingPO"].ToString()).ToString()); }
                    temp += string.Format("'{0}',", dtPlant.Rows[i]["TradingPO"].ToString());
                }
                if (temp.Length > 1)
                {
                    modelT.contractNos = temp;
                    PlantUpdate(modelT, 2);
                }
                nStart = nEnd;
            }
            #endregion
        }
        #region 重复的TradingPO
        if (dts[2] != null && dts[2].Rows.Count > 0)
        {
            DataTable tr = dts[2].Clone();
            string temp = string.Empty;
            foreach (DataRow row in dts[2].Rows)
            {
                tr.Clear();
                DataRow tRow = tr.NewRow();
                tRow["ContractNoMain"] = row["ContractNoMain"];
                tRow["ContractNoCust"] = row["ContractNoCust"];
                tRow["TradingPO"] = row["TradingPO"];

                tr.Rows.Add(tRow);

                temp = null;
                if (CommonMethod.CheckNUM(row["TradingPO"].ToString()))
                { temp += string.Format("'{0}',", Convert.ToInt64(row["TradingPO"])); }
                temp += string.Format("'{0}',", row["TradingPO"]);
                if (temp.Length > 1)
                {
                    modelT.ContractNoForPlantUpdateDT = tr;
                    modelT.contractNos = temp;
                    PlantUpdate(modelT, 2);
                }
            }
        }
        #endregion

        #endregion
    }

    public DataTable[] UpdateTradingPlantGetNos()
    {
        return help.RunProcGetTables("UpdateTradingPlantGetNos", null);
    }
    public string GetAllContractNo(DataRow[] rows)
    {
        string temp = string.Empty;
        foreach (DataRow row in rows)
        {
            if (CommonMethod.CheckNUM(row["ContractNoMain"].ToString()))
            { temp += string.Format("'{0}',", Convert.ToInt64(row["ContractNoMain"].ToString()).ToString()); }
            temp += string.Format("'{0}',", row["ContractNoMain"].ToString());
        }
        return temp;
    }
    public string GetAllContractNo(DataTable dt,string columnName)
    {
        string temp = string.Empty;
        if (dt != null && dt.Rows.Count > 0)
        {
            foreach (DataRow row in dt.Rows)
            {
                if (CommonMethod.CheckNUM(row[columnName].ToString()))
                { temp += string.Format("'{0}',", Convert.ToInt64(row[columnName].ToString()).ToString()); }
                temp += string.Format("'{0}',", row[columnName].ToString());
            }
        }
        return temp;
    }
    public DataTable GetAllContractNoDt()
    {
        string sql = " select distinct ContractNoMain,AddDate from M_Contract where ContractNoMain is not null and len(ContractNoMain)>0 and DeleteFlag<>1 order by AddDate desc ";
        DataTable dt = help.GetDataTable(sql);
        
        return dt;
    }
    public DataTable GetSoAndLineNo(string contractNos)
    {
        string temp = string.Empty;
        string sql = @"select distinct ContractNoMain,ContractNoCust,TradingPO,TradingPOLine
            from   M_SO_Trading
            where  len(TradingPO)>0 and len(TradingPOLine)>0 " + contractNos;
        DataTable dt = help.GetDataTable(sql);
        //if (dt != null && dt.Rows.Count > 0)
        //{        }
        return dt;
    }
}